using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Model;
using SEO.DBSqlHelp;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，SEO ADIN
    /// 内容摘要: ArticlesDAL
    /// 完成日期：2011年6月26日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class ArticlesDAL
    {

        /// <summary>
        /// 获取Articles表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<Articles> GetAllArticlesDAL()
        {
            List<Articles> list = new List<Articles>();
            string strSql = "select * from Articles";
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.DBHelper.ConnectionTouZiWeiShi());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Articles asnew = new Articles();
                    asnew.ArticleId = int.Parse(dr["article_id"].ToString());
                    asnew.ArticleTitle = dr["article_title"].ToString();
                    asnew.ArticleAuthor = dr["article_author"].ToString();
                    asnew.ArticleType = dr["article_type"].ToString();
                    asnew.ArticleContent = dr["article_content"].ToString();
                    asnew.PostStatus = int.Parse(dr["post_status"].ToString());
                    asnew.ClickNum = int.Parse(dr["click_num"].ToString());
                    asnew.AddDate = Convert.ToDateTime(dr["add_date"].ToString());

                    list.Add(asnew);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取Articles表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<Articles> GetArticlesByWhereDAL(string strSql)
        {
            List<Articles> list = new List<Articles>();
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.DBHelper.ConnectionTouZiWeiShi());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Articles asnew = new Articles();
                    asnew.ArticleId = int.Parse(dr["article_id"].ToString());
                    asnew.ArticleTitle = dr["article_title"].ToString();
                    asnew.ArticleAuthor = dr["article_author"].ToString();
                    asnew.ArticleType = dr["article_type"].ToString();
                    asnew.ArticleContent = dr["article_content"].ToString();
                    asnew.PostStatus = int.Parse(dr["post_status"].ToString());
                    asnew.ClickNum = int.Parse(dr["click_num"].ToString());
                    asnew.AddDate = Convert.ToDateTime(dr["add_date"].ToString());

                    list.Add(asnew);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往Articles表中插入数据
        /// <param name="asnew">实例化Articles类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertArticlesDAL(SEO.Model.Articles asnew)
        {
            int iNum = 0;
            string strSql = "insert into Articles(article_title,article_author,article_type,article_content,post_status) " +
             " values('" + asnew.ArticleTitle + "','" + asnew.ArticleAuthor + "','" + asnew.ArticleType + "','" + asnew.ArticleContent + "'," + asnew.PostStatus + ")";
            try
            {
                iNum = SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除Articles表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteArticlesDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新Articles表中数据
        /// </summary>
        /// <param name="asnew">实例化Articles类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateArticlesDAL(SEO.Model.Articles asnew)
        {
            int iNum = 0;
            string strSql = "update Articles set click_num=click_num+1 where article_id=" + asnew.ArticleId;
            try
            {
                iNum = SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表Articles到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindArticlesDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.DBHelper.ConnectionTouZiWeiShi());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "Articles");
                //ds.Tables["表名"].Columns.Add("字段名", typeof(string));
                for (int i = 0; i < ds.Tables["Articles"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["Articles"].Rows[i];
                    //dr["字段名"] = "字段值";
                }
                dgv.DataSource = ds.Tables["Articles"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Articles -> " + ex.Message);
            }
        }

    }
}
